package com.navi.mould1.service.impl;
import com.navi.bean.out.ConditionOption;
import com.navi.core.context.HistoryContext;
import com.navi.core.repository.NaviRepository;
import com.navi.core.repository.impl.MySqlRepositoryImpl;
import com.navi.core.util.JacksonUtil;
import com.navi.core.util.JdbcUtils;
import com.navi.core.util.LoggerUtil;
import com.navi.bean.sql.SqlVo;
import com.navi.mould1.service.IConditionService;
import com.navi.mould1.tx.xpcondition.XpconditionI;
import com.navi.mould1.tx.xpcondition.XpconditionO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

import static com.navi.bean.sql.SqlVo.jointInSql;
import static com.navi.mould1.reportUtils.reportUtil.compareTo2;

@Slf4j
@Service
public class ConditionServiceImpl implements IConditionService {
    private Logger logger = LoggerFactory.getLogger(this.getClass());

    QueryRunner qr = new QueryRunner();

    @Autowired
    private NaviRepository dao;

    @Autowired
    private MySqlRepositoryImpl mySqlRepository;

    @Override
        public String subMainProc(String strInObj) {
        HistoryContext.clear(this.getClass());

        XpconditionI inTrx = new XpconditionI();
        XpconditionO outTrx = new XpconditionO();

        inTrx = JacksonUtil.fromJson(strInObj, XpconditionI.class);
        logger.info(strInObj);

        String actionFlg = inTrx.getAction_flg();
        switch (actionFlg) {
            case "FBID":
                outTrx = fabId_findProc(inTrx);
                break;
            case "AREA":
                outTrx = bayId_findProc(inTrx);
                break;
            case "IVLINE":
                outTrx = ivLine_findProc(inTrx);
                break;
            case "OPE":
                outTrx = opeId_findProc(inTrx);
                break;
            case "SHIF":
                outTrx = shiftId_findProc(inTrx);
                break;
            case "TOOL":
                outTrx = toolId_findProc(inTrx);
                break;
            case "TOLG":
                outTrx = toolgId_findProc(inTrx);
                break;
            case "DATAGROUP":
                outTrx = dataGroupId_findProc(inTrx);
                break;
            case "CCHARTGRP":
                outTrx = controlChartGrp_findProc(inTrx);
                break;
            case "CCHART":
                outTrx = controlChart_findProc(inTrx);
                break;
            case "BOATNUMBER":
                outTrx = boatNumber_findProc(inTrx);
                break;
            case "LOT":
                outTrx = lotNumber_findProc(inTrx);
                break;
            case "SPEC":
                outTrx = spec_findProc(inTrx);
                break;
            case "WAFERSPEC":
                outTrx = waferSpec_findProc(inTrx);
                break;
            case "MAFACT":
                outTrx = manufacturer_findProc(inTrx);
                break;
            case "BASEID":
                outTrx = baseId_findProc(inTrx);
                break;
            case "EFFNO":
                outTrx = effNo_findProc(inTrx);
                break;
            case "ATTRIBUTES":
                outTrx = attributes_findProc(inTrx);
                break;
            case "GEAR":
                outTrx = gear_findProc(inTrx);
                break;
            case "BIN":
                outTrx = bin_findProc(inTrx);
                break;
            case "LOTID":
                outTrx = lotId_findProc(inTrx);
                break;
            case "LSWH":
                outTrx = mtrlFabId_findProc(inTrx);
                break;
            case "MTRLPRODID":
                outTrx = mtrlProdId_findProc(inTrx);
                break;
            case "ZHNUMBER":
                outTrx = zhNumber_findProc(inTrx);
                break;
            case "ZHCOUNT":
                outTrx = zhCount_findProc(inTrx);
                break;
            case "CCE":
                outTrx = componentCellEfficiency_findProc(inTrx);
                break;
            case "PRODMTRL":
                outTrx = productMaterial_findProc(inTrx);
                break;
            case "BOMMTRL":
                outTrx = bomMaterial_findProc(inTrx);
                break;
            default:
                break;
        }

        final String strOutTrx = JacksonUtil.toJSONStr(outTrx);
        return strOutTrx;
    }


    /*** 查询厂别 **/
    private XpconditionO fabId_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select data_seq_id as id, data_desc as text, data_ext as value from icim_jn.bis_data where data_cate='FBID'");
        if (StringUtils.isNotEmpty(inTrx.getFab_id()) && !inTrx.getFab_id().equals("null")) {
            sqlVo.appendSql("and data_ext in" + jointInSql(inTrx.getFab_id()));
        }
        if ("自动点巡检报表".equals(inTrx.getReport_name()) || "IV工艺测试Chart图报表".equals(inTrx.getReport_name())) {
            sqlVo.appendSql("and data_ext != '101A' and data_ext != '实验室'");
        }
        if (Arrays.asList("halm机测试 - 颜色比例图表", "halm机测试 - Eta效能趋势报表", "halm机测试 - 档位分布报表",
                "halm机测试 - 小时效率趋势报表", "halm机测试 - 线别效能报表", "halm机测试 - 批次效能报表").contains(inTrx.getReport_name())) {
            sqlVo.appendSql("and data_ext = '101A'");
        }
        sqlVo.appendSql("order by data_desc");
        return mySql_query(sqlVo);
    }

    /*** 查询线别 **/
    private XpconditionO bayId_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo(" select data_seq_id as id, data_desc as text, data_ext as value from icim_jn.bis_data where data_cate='AREA'");
        if (StringUtils.isNotEmpty(inTrx.getFab_id())) {
            sqlVo.appendSql("and data_id in" + jointInSql(inTrx.getFab_id()));
        }
        if (StringUtils.isNotEmpty(inTrx.getEff_no())) {
            XpconditionO out = mySql_query(new SqlVo("select ext_1 as value from bis_data where data_cate='FDBB' and data_id in" + jointInSql(inTrx.getEff_no())));
            if (CollectionUtils.isNotEmpty(out.getOary())) {
                if (StringUtils.isNotEmpty(out.getOary().get(0).getValue())) {
                    sqlVo.appendSql("and data_ext in" + jointInSql(out.getOary().get(0).getValue()));
                }
            }
        }
        if ("PECVD膜厚对比报表".equals(inTrx.getReport_name()) || "PECVD膜厚等级比例报表".equals(inTrx.getReport_name())
                || "PECVD膜厚值分布报表".equals(inTrx.getReport_name()) || "PECVD膜厚值ID查询报表".equals(inTrx.getReport_name())) {
            sqlVo.appendSql("and data_id != '102A' and data_id != '102B'");
        }
        if ("IV工艺测试Chart图报表".equals(inTrx.getReport_name())) {
            sqlVo.appendSql("and data_id != '101A'");
        }
        sqlVo.appendSql("order by data_desc");
        return mySql_query(sqlVo);
    }

    /*** 查询IV线别 **/
    private XpconditionO ivLine_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo(" select ext_1 as id, ext_2 as text, ext_1 as value from icim_jn.bis_data where data_cate='IVTL'");
        sqlVo.appendSql("order by ext_2");
        return mySql_query(sqlVo);
    }

    /*** 查询站点 **/
    private XpconditionO opeId_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo(" select ope_id as id, ope_dsc as text, ope_id as value from icim_jn.bis_ope where 1=1");
        if (StringUtils.isNotEmpty(inTrx.getFab_id())) {
            sqlVo.appendSql("and fab_id_fk in" + jointInSql(inTrx.getFab_id()));
        }
        sqlVo.appendSql("order by ope_dsc");
        return mySql_query(sqlVo);
    }

    /*** 查询设备组 **/
    private XpconditionO toolgId_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select data_ext as id, data_desc as text, data_ext as value from icim_jn.bis_data where data_cate='TOLG'");
        if (StringUtils.isNotEmpty(inTrx.getFab_id())) {
            sqlVo.appendSql("and data_item in" + jointInSql(inTrx.getFab_id()));
        }
        //if (LKingStringUtils.isNotEmpty(inTrx.getOpe_id())) {
        //    sqlVo.appendSql("and root_tool_id in (select tool_id_fk from bis_tool_gr where toolg_id in " +
        //            "(select toolg_id_fk from icim_jn.bis_ope where ope_ver = '00000' and ope_id in " + jointInSql(inTrx.getOpe_id()) + "))");
        //}
        sqlVo.appendSql("order by data_desc");
        return mySql_query(sqlVo);
    }

    /*** 查询设备 **/
    private XpconditionO toolId_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select tool_id as id, tool_dsc as text, tool_id as value from icim_jn.bis_tool where 1=1");
        if (StringUtils.isNotEmpty(inTrx.getFab_id())) {
            sqlVo.appendSql("and fab_id_fk in" + jointInSql(inTrx.getFab_id()));
        }
        if (StringUtils.isNotEmpty(inTrx.getBay_id())) {
            sqlVo.appendSql("and bay_id in" + jointInSql(inTrx.getBay_id()));
        }
        if (StringUtils.isNotEmpty(inTrx.getOpe_id())) {
            sqlVo.appendSql("and root_tool_id in (select tool_id_fk from bis_tool_gr where toolg_id in " +
                    "(select toolg_id_fk from icim_jn.bis_ope where ope_ver = '00000' and ope_id in " + jointInSql(inTrx.getOpe_id()) + "))");
        }
        if (StringUtils.isNotEmpty(inTrx.getToolg_id())) {
            sqlVo.appendSql("and root_tool_id in (select tool_id_fk from bis_tool_gr where toolg_id in " + jointInSql(inTrx.getToolg_id()) + ")");
        }
        if (StringUtils.isNotEmpty(inTrx.getTool_type()) && ("MAIN".equals(inTrx.getTool_type()) || "SUB".equals(inTrx.getTool_type()))) {
            sqlVo.appendSql("and unit_typ in" + jointInSql(inTrx.getTool_type()));
        }
        if (StringUtils.isNotEmpty(inTrx.getSpc_flg())) {
            sqlVo.appendSql("and spc_flg in" + jointInSql(inTrx.getSpc_flg()));
        }
        if (StringUtils.isNotEmpty(inTrx.getRoot_tool_id())) {
            sqlVo.appendSql("and root_tool_id in" + jointInSql(inTrx.getRoot_tool_id()));
        }
        if (StringUtils.isNotEmpty(inTrx.getTool_main_id())) {
            sqlVo.appendSql("and root_tool_id in" + jointInSql(inTrx.getTool_main_id()));
        }
        if (StringUtils.isNotEmpty(inTrx.getTool_sub_id())) {
            sqlVo.appendSql("and tool_id in" + jointInSql(inTrx.getTool_sub_id()));
        }
        if (StringUtils.isNotEmpty(inTrx.getTool_id())) {
            sqlVo.appendSql("and tool_id in" + jointInSql(inTrx.getTool_id()));
        }
        if ("IV工艺测试Chart图报表".equals(inTrx.getReport_name())) {
            sqlVo.appendSql("and root_tool_id in (select tool_id_fk from bis_tool_gr where toolg_id in('102A-IV','102B-IV'))");
        }
        if ("稼动率报表".equals(inTrx.getReport_name()) || "稼动率原因代码报表".equals(inTrx.getReport_name())) {
            sqlVo.appendSql("and ((unit_typ = 'MAIN' and tool_cate = 'PROC') OR sum_flg = 'Y')");
            //sqlVo.appendSql("and unit_typ = 'MAIN' and tool_cate = 'PROC'");
        }
        if ("PECVD膜厚对比报表".equals(inTrx.getReport_name()) || "PECVD膜厚等级比例报表".equals(inTrx.getReport_name())
                || "PECVD膜厚值分布报表".equals(inTrx.getReport_name()) || "PECVD膜厚值ID查询报表".equals(inTrx.getReport_name())) {
            sqlVo.appendSql("and root_tool_id in (select tool_id_fk from bis_tool_gr where toolg_id = '$TPECVD')");
        }
        //sqlVo.appendSql("and pms_flg = 'Y'");
        sqlVo.appendSql("order by tool_dsc");
        return mySql_query(sqlVo);
    }

    /*** 查询班别 **/
    private XpconditionO shiftId_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select data_seq_id as id, data_desc as text, data_id as value from icim_jn.bis_data where data_cate='SHIF' order by data_desc");
        return mySql_query(sqlVo);
    }

    /*** 查询量测名称 **/
    private XpconditionO dataGroupId_findProc(XpconditionI inTrx) {
        if (StringUtils.isNotEmpty(inTrx.getData_group_tbl()) && "spc_ret_sum_rpt".equals(inTrx.getData_group_tbl())) {
            final SqlVo sqlVo = new SqlVo("select distinct(data_group_fk) as value from icim_jn.spc_ret_sum_rpt order by data_group_fk");
            XpconditionO outTrx = mySql_query(sqlVo);
            outTrx.getOary().forEach(data -> {
                data.setId(data.getValue());
                data.setText(data.getValue());
            });
            return outTrx;
        }
        final SqlVo sqlVo = new SqlVo("select data_ext as id, data_desc as text, data_ext as value from bis_data where data_cate = 'MLGP'");
        return mySql_query(sqlVo);
    }

    /*** 查询管制图组 **/
    private XpconditionO controlChartGrp_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo();
//        if (StringUtils.isNotEmpty(inTrx.getReport_name()) && "SPC Chart图报表".equals(inTrx.getReport_name())) {
            sqlVo.appendSql("select grp_no as id, grp_dsc as text, concat(grp_no,'@',col_typ,'@',grp_name) as value from spc_bis_grp_main WHERE 1=1");
//        }else {
//            sqlVo.appendSql("select grp_no as id, grp_dsc as text, grp_name as value from spc_bis_grp_main WHERE 1=1");
//        }
        List<ConditionOption> tool_list = toolId_findProc(inTrx).getOary();
        if (CollectionUtils.isNotEmpty(tool_list) && tool_list.size() > 0) {
            String tool_join_str = tool_list.stream().distinct().map(data -> "'" + data.getValue() + "'").collect(Collectors.joining(",","(",")"));
            sqlVo.appendSql("and (col_typ, grp_no) in (select col_typ_fk, grp_no_fk from spc_bis_grp_dt where tool_id_fk in" + tool_join_str + ")");
        }
        if (StringUtils.isNotEmpty(inTrx.getCollect_type())) {
            sqlVo.appendSql("and col_typ in" + jointInSql(inTrx.getCollect_type()));
        }
        sqlVo.appendSql("order by grp_dsc");
        return mySql_query(sqlVo);
    }

    /*** 查询管制图 **/
    private XpconditionO controlChart_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select chart_no as id, chart_dsc as text, concat(chart_no,'@',chart_typ,'@',chart_name) as value from spc_bis_chart_main A ");
        sqlVo.appendSql("LEFT JOIN spc_bis_grp_main B ON A.COL_TYP_FK = B.COL_TYP AND A.GRP_NO_FK = B.GRP_NO");
        sqlVo.appendSql("WHERE 1=1 ");
        if (StringUtils.isNotEmpty(inTrx.getControl_chart_grp())) {
//            if (StringUtils.isNotEmpty(inTrx.getReport_name()) && "SPC Chart图报表".equals(inTrx.getReport_name())) {
                List<String> grpNo_list = new ArrayList<>();
                List<String> colType_list = new ArrayList<>();
                for (String grpNo_colTyp : inTrx.getControl_chart_grp().split(",")) {
                    grpNo_list.add(grpNo_colTyp.split("@")[0]);
                    colType_list.add(grpNo_colTyp.split("@")[1]);
                }
                sqlVo.appendSql("and B.col_typ in" + jointInSql(colType_list.stream().distinct().collect(Collectors.joining(","))));
                sqlVo.appendSql("and B.grp_no in" + jointInSql(grpNo_list.stream().distinct().collect(Collectors.joining(","))));
//            }else {
//                sqlVo.appendSql("and B.grp_name in" + jointInSql(inTrx.getControl_chart_grp()));
//            }
        }
        if (StringUtils.isNotEmpty(inTrx.getReport_name()) && ("SPC CPK分析报表".equals(inTrx.getReport_name()) || "SPC PPK分析报表".equals(inTrx.getReport_name()))) {
            sqlVo.appendSql("and A.calc_cpk_flg = 'Y'");
        }else if (StringUtils.isNotEmpty(inTrx.getReport_name()) && "SPC控制线建议值报表".equals(inTrx.getReport_name())) {
            sqlVo.appendSql("and A.calc_cl_flg = 'Y'");
        }
        //if (StringUtils.isNotEmpty(inTrx.getData_group())) {
        //    sqlVo.appendSql("and A.data_group_fk in" + jointInSql(inTrx.getData_group()));
        //}
        sqlVo.appendSql("order by chart_dsc");
        return mySql_query(sqlVo);
    }

    /*** 查询舟号 **/
    private XpconditionO boatNumber_findProc(XpconditionI inTrx) {
        List<ConditionOption> conditionOptions = new ArrayList<>();
        if (StringUtils.isNotEmpty(inTrx.getRoot_tool_id())) {
            conditionOptions.addAll(mySql_query(new SqlVo("select toolg_id as value from bis_tool_gr where tool_id_fk in" + jointInSql(inTrx.getRoot_tool_id()))).getOary());
        }
        if (StringUtils.isNotEmpty(inTrx.getTool_id())) {
            conditionOptions.addAll(mySql_query(new SqlVo("select toolg_id as value from bis_tool_gr where tool_id_fk in (select root_tool_id from icim_jn.bis_tool where tool_id in" + jointInSql(inTrx.getTool_id()) + ")")).getOary());
        }
        if (conditionOptions.stream().filter(data -> "$TPECVD".equals(data.getValue())).collect(Collectors.toList()).size() > 0) {
            return mySql_query(new SqlVo("select data_id as id, data_desc as text, data_id as value from icim_jn.bis_data where data_cate='ZH' order by data_desc"));
        }else {
            XpconditionO outTrx = new XpconditionO();
            outTrx.setRtn_code("0000000");
            outTrx.setRtn_mesg("success");
            outTrx.setOary(null);
            outTrx.setTbl_cnt(0);
            return outTrx;
        }
    }

    /*** 查询校准板 **/
    private XpconditionO lotNumber_findProc(XpconditionI inTrx) {
        //final SqlVo sqlVo = new SqlVo("select tab.lot_number as id, tab.lot_number as text, tab.lot_number as value from(" +
        //        "select distinct(lot_number) from icim_jn.iv_data_set where 1=1");
        //if (StringUtils.isNotEmpty(inTrx.getTool_id())) {
        //    sqlVo.appendSql("and tool_id_fk in" + jointInSql(inTrx.getTool_id()));
        //}
        final SqlVo sqlVo = new SqlVo("select tab.lot_number as id, tab.lot_number as text, tab.lot_number as value from(" +
                "select distinct(lot_number) from icim_jn.iv_tester_data where 1=1");
        if (StringUtils.isNotEmpty(inTrx.getTool_id())) {
            sqlVo.appendSql("and tool_id_fk in" + jointInSql(inTrx.getTool_id()));
        }
        if (StringUtils.isNotEmpty(inTrx.getStart_time())) sqlVo.appendSqlAndAddParam("and test_time >= ?", inTrx.getStart_time());
        if (StringUtils.isNotEmpty(inTrx.getEnd_time())) sqlVo.appendSqlAndAddParam("and test_time <= ?", inTrx.getEnd_time());
        sqlVo.appendSql("order by lot_number)tab");
        return mySql_query(sqlVo);
    }

    /*** 规格 **/
    private XpconditionO spec_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select data_ext as id, data_ext as text, data_ext as value from bis_data where data_cate='WFSF' group by data_ext order by data_ext");
        return mySql_query(sqlVo);
    }

    /*** 硅片规格 **/
    private XpconditionO waferSpec_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select data_id as id, data_desc as text, data_id as value from bis_data where data_cate='WFSF'");
        if (StringUtils.isNotEmpty(inTrx.getSpec())) {
            sqlVo.appendSql("and data_ext in" + jointInSql(inTrx.getSpec()));
        }
        sqlVo.appendSql("order by data_desc");
        return mySql_query(sqlVo);
    }

    /*** 厂家 **/
    private XpconditionO manufacturer_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select data_id as id, data_desc as text, data_id as value from icim_jn.bis_data where data_cate='CMRL'");
        sqlVo.appendSql("group by data_id");
        sqlVo.appendSql("order by data_desc");
        return mySql_query(sqlVo);
    }

    /*** 基地 **/
    private XpconditionO baseId_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select ext_1 as id, ext_2 as text, ext_1 as value from icim_jn.bis_data where data_cate='CMRL'");
        if (StringUtils.isNotEmpty(inTrx.getManufacturer())) {
            sqlVo.appendSql("and data_id in" + jointInSql(inTrx.getManufacturer()));
        }
        sqlVo.appendSql("order by ext_2");
        return mySql_query(sqlVo);
    }

    /*** 分档版本 **/
    private XpconditionO effNo_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select tbl.data_ext as id, tbl.data_ext as text, tbl.data_ext as value from(select distinct(data_ext) from icim_jn.bis_data where data_cate='EFGR' and data_ext is not null order by data_ext) tbl");
        return mySql_query(sqlVo);
    }

    /*** 单/多晶 **/
    private XpconditionO attributes_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select data_id as id, data_id as text, data_id as value from bis_data where data_cate='EFGR'");
        if (StringUtils.isNotEmpty(inTrx.getEff_no())) {
            sqlVo.appendSql("and data_ext in" + jointInSql(inTrx.getEff_no()));
        }
        sqlVo.appendSql("GROUP BY data_id ORDER BY data_id");
        return mySql_query(sqlVo);
    }

    /*** 档位 **/
    private XpconditionO gear_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select ext_1 as id, ext_1 as text, ext_1 as value from bis_data where data_cate='EFGR'");
        if (StringUtils.isNotEmpty(inTrx.getEff_no())) {
            sqlVo.appendSql("and data_ext in" + jointInSql(inTrx.getEff_no()));
        }
        if (StringUtils.isNotEmpty(inTrx.getAttributes())) {
            sqlVo.appendSql("and data_id in" + jointInSql(inTrx.getAttributes()));
        }
        sqlVo.appendSql("GROUP BY ext_1 ORDER BY ext_1");
        return mySql_query(sqlVo);
    }

    /*** Bin值档位 **/
    private XpconditionO bin_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select ext_2 as id, ext_2 as text, ext_2 as value from icim_jn.bis_data where data_cate='EFGR'");
        if (StringUtils.isNotEmpty(inTrx.getEff_no())) {
            sqlVo.appendSql("and data_ext in" + jointInSql(inTrx.getEff_no()));
        }
        if (StringUtils.isNotEmpty(inTrx.getAttributes())) {
            sqlVo.appendSql("and data_id in" + jointInSql(inTrx.getAttributes()));
        }
        sqlVo.appendSql("GROUP BY ext_2 ORDER BY ext_2");
        return mySql_query(sqlVo);
    }

    /*** 批次 **/
    private XpconditionO lotId_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select tbl.lot_id as id, tbl.lot_id as text, tbl.lot_id as value from(");
        sqlVo.appendSql("select distinct(lot_id) from halm.halm_test_data where 1=1");
        if (StringUtils.isNotEmpty(inTrx.getStart_time())) sqlVo.appendSqlAndAddParam("and input_timestamp >= ?", inTrx.getStart_time());
        if (StringUtils.isNotEmpty(inTrx.getEnd_time())) sqlVo.appendSqlAndAddParam("and input_timestamp <= ?", inTrx.getEnd_time());
        if (StringUtils.isNotEmpty(inTrx.getAttributes())) sqlVo.appendSql("and attributes in" + jointInSql(inTrx.getAttributes()));
        sqlVo.appendSql("ORDER BY lot_id) tbl");
        return mySql_query(sqlVo);
    }

    /*** 备品备件仓 **/
    private XpconditionO mtrlFabId_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select data_ext as id, ext_1 as text, data_ext as value from icim_jn.bis_data where data_cate = 'LSWH' AND data_id = '5' order by ext_1");
        return mySql_query(sqlVo);
    }

    /*** 物料号 **/
    private XpconditionO mtrlProdId_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select mtrl_code as id, mtrl_name as text, mtrl_code as value from icim_jn.bis_mtrl order by mtrl_name");
        return mySql_query(sqlVo);
    }

    /*** PECVD 舟号 **/
    private XpconditionO zhNumber_findProc(XpconditionI inTrx) {
        //final SqlVo sqlVo = new SqlVo("select distinct(substring(data_value_389, 3, 3)) as text from icim_jn.spc_ret_mfg_data_pecvd where 1=1");
        //if (StringUtils.isNotEmpty(inTrx.getBay_id())) sqlVo.appendSql("and line_id_fk in" + jointInSql(inTrx.getBay_id()));
        //if (StringUtils.isNotEmpty(inTrx.getTool_id())) sqlVo.appendSql("and tool_id_fk in" + jointInSql(inTrx.getTool_id()));
        //if (StringUtils.isNotEmpty(inTrx.getPipe_number())) sqlVo.appendSql("and substring(data_value_389, 6, 1) in" + jointInSql(inTrx.getPipe_number()));
        //XpconditionO outTrx = mySql_query(sqlVo);
        //outTrx.setOary(
        //    outTrx.getOary().stream().map(data -> {
        //        data.setId(data.getText());
        //        data.setValue(data.getText());
        //        return data;
        //    }).sorted((o1, o2) -> {
        //        if (StringUtils.isEmpty(o1.getText())) return -1;
        //        if (StringUtils.isEmpty(o2.getText())) return 1;
        //        return Integer.parseInt(o1.getText()) - Integer.parseInt(o2.getText());
        //    }).collect(Collectors.toList())
        //);
        //outTrx.setTbl_cnt(outTrx.getOary().size());
        //return outTrx;

        final SqlVo sqlVo = new SqlVo("select tbl.zh_number as id, tbl.zh_number as text, tbl.zh_number as value from(");
            sqlVo.appendSql("select distinct(zh_number) from icim_jn.pecvd_basic_data where 1=1");
            if (StringUtils.isNotEmpty(inTrx.getBay_id())) sqlVo.appendSql("and line_id_fk in" + jointInSql(inTrx.getBay_id()));
            if (StringUtils.isNotEmpty(inTrx.getTool_id())) sqlVo.appendSql("and tool_id_fk in" + jointInSql(inTrx.getTool_id()));
            if (StringUtils.isNotEmpty(inTrx.getPipe_number())) sqlVo.appendSql("and pipe_number in" + jointInSql(inTrx.getPipe_number()));
            sqlVo.appendSql("and zh_number is not null");
        sqlVo.appendSql(") tbl");
        XpconditionO outTrx = mySql_query(sqlVo);
        outTrx.setOary(
                outTrx.getOary().stream().sorted((o1, o2) -> {
                    if (StringUtils.isEmpty(o1.getText())) return -1;
                    if (StringUtils.isEmpty(o2.getText())) return 1;
                    return Integer.parseInt(o1.getText()) - Integer.parseInt(o2.getText());
                }).collect(Collectors.toList())
        );
        outTrx.setTbl_cnt(outTrx.getOary().size());
        return outTrx;
    }

    /*** PECVD 舟使用次数 **/
    private XpconditionO zhCount_findProc(XpconditionI inTrx) {
        //final SqlVo sqlVo = new SqlVo("select distinct(substring(data_value_389, 7, 3)) as text from icim_jn.spc_ret_mfg_data_pecvd where 1=1");
        //if (StringUtils.isNotEmpty(inTrx.getBay_id())) sqlVo.appendSql("and line_id_fk in" + jointInSql(inTrx.getBay_id()));
        //if (StringUtils.isNotEmpty(inTrx.getTool_id())) sqlVo.appendSql("and tool_id_fk in" + jointInSql(inTrx.getTool_id()));
        //if (StringUtils.isNotEmpty(inTrx.getPipe_number())) sqlVo.appendSql("and substring(data_value_389, 6, 1) in" + jointInSql(inTrx.getPipe_number()));
        //if (StringUtils.isNotEmpty(inTrx.getZh_number())) sqlVo.appendSql("and substring(data_value_389, 3, 3) in" + jointInSql(inTrx.getZh_number()));
        //XpconditionO outTrx = mySql_query(sqlVo);
        //outTrx.setOary(
        //        outTrx.getOary().stream().map(data -> {
        //            data.setId(data.getText());
        //            data.setValue(data.getText());
        //            return data;
        //        }).sorted((o1, o2) -> {
        //            if (StringUtils.isEmpty(o1.getText())) return -1;
        //            if (StringUtils.isEmpty(o2.getText())) return 1;
        //            return Integer.parseInt(o1.getText()) - Integer.parseInt(o2.getText());
        //        }).collect(Collectors.toList())
        //);
        //outTrx.setTbl_cnt(outTrx.getOary().size());
        //return outTrx;

        final SqlVo sqlVo = new SqlVo("select zh_count as id, zh_count as text, zh_count as value from (");
        sqlVo.appendSql("select distinct(zh_count) from icim_jn.pecvd_basic_data where 1=1");
        if (StringUtils.isNotEmpty(inTrx.getBay_id())) sqlVo.appendSql("and line_id_fk in" + jointInSql(inTrx.getBay_id()));
        if (StringUtils.isNotEmpty(inTrx.getTool_id())) sqlVo.appendSql("and tool_id_fk in" + jointInSql(inTrx.getTool_id()));
        if (StringUtils.isNotEmpty(inTrx.getPipe_number())) sqlVo.appendSql("and pipe_number in" + jointInSql(inTrx.getPipe_number()));
        if (StringUtils.isNotEmpty(inTrx.getZh_number())) sqlVo.appendSql("and zh_number in" + jointInSql(inTrx.getZh_number()));
        sqlVo.appendSql("and zh_count is not null");
        sqlVo.appendSql(") tbl");
        XpconditionO outTrx = mySql_query(sqlVo);
        outTrx.setOary(
                outTrx.getOary().stream().sorted((o1, o2) -> {
                    if (StringUtils.isEmpty(o1.getText())) return -1;
                    if (StringUtils.isEmpty(o2.getText())) return 1;
                    return Integer.parseInt(o1.getText()) - Integer.parseInt(o2.getText());
                }).collect(Collectors.toList())
        );
        outTrx.setTbl_cnt(outTrx.getOary().size());
        return outTrx;
    }

    /*** 组件电池片效率 **/
    private XpconditionO componentCellEfficiency_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select tbl.attr_1 as id, tbl.attr_1 as text, tbl.attr_1 as value from(select distinct(attr_1) as attr_1 from dbo.iv_distribution WHERE 1=1 ");
        if (StringUtils.isNotEmpty(inTrx.getBay_id())) sqlVo.appendSql("and equipment_code in" + jointInSql(inTrx.getBay_id()));
        if (StringUtils.isNotEmpty(inTrx.getStart_time())) sqlVo.appendSqlAndAddParam("and test_time >= ?", inTrx.getStart_time());
        if (StringUtils.isNotEmpty(inTrx.getEnd_time())) sqlVo.appendSqlAndAddParam("and test_time <= ?", inTrx.getEnd_time());
        sqlVo.appendSql(")tbl ORDER BY text");
        XpconditionO outTrx = sqlServer_query(sqlVo);
        List<ConditionOption> oary = new ArrayList<>();
        outTrx.getOary().stream().filter(data -> StringUtils.isNotEmpty(data.getValue())).map(data -> {
            String value = data.getValue().trim().replace(" ", "").replace("%", "");
            return value.endsWith("0") && !value.endsWith(".0") ? value.substring(0, value.length() - 1) : value;
        }).distinct().sorted().collect(Collectors.toList()).forEach(data -> {
            oary.add(new ConditionOption(data, data, data));
        });
        outTrx.setOary(oary);
        outTrx.setTbl_cnt(outTrx.getOary().size());
        return outTrx;

        //final SqlVo sqlVo = new SqlVo("select attr_1 as id, attr_1 as text, attr_1 as value from icim_jn.iv_attr1_data order by attr_1");
        //return mySql_query(sqlVo);
    }

    /*** 产品料号 **/
    private XpconditionO productMaterial_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select tbl.material_code as id, tbl.material_code as text, tbl.material_code as value from(select distinct(material_code) as material_code from dbo.iv_distribution WHERE 1=1 ");
        if (StringUtils.isNotEmpty(inTrx.getBay_id())) sqlVo.appendSql("and equipment_code in" + jointInSql(inTrx.getBay_id()));
        if (StringUtils.isNotEmpty(inTrx.getStart_time())) sqlVo.appendSql("and test_time >= '" + inTrx.getStart_time() + ":00:00'");
        if (StringUtils.isNotEmpty(inTrx.getEnd_time())) sqlVo.appendSql("and test_time <= '" + inTrx.getEnd_time() + ":00:00'");
        sqlVo.appendSql(")tbl ORDER BY text");
        return sqlServer_query(sqlVo);
    }

    /*** 原材料料号 **/
    private XpconditionO bomMaterial_findProc(XpconditionI inTrx) {
        final SqlVo sqlVo = new SqlVo("select tbl.bom_material_code as id, tbl.bom_material_code as text, tbl.bom_material_code as value from(select distinct(bom_material_code) as bom_material_code from dbo.iv_distribution WHERE 1=1 ");
        if (StringUtils.isNotEmpty(inTrx.getBay_id())) sqlVo.appendSql("and equipment_code in" + jointInSql(inTrx.getBay_id()));
        if (StringUtils.isNotEmpty(inTrx.getProduct_material())) sqlVo.appendSql("and material_code in" + jointInSql(inTrx.getProduct_material()));
        if (StringUtils.isNotEmpty(inTrx.getStart_time())) sqlVo.appendSql("and test_time >= '" + inTrx.getStart_time() + ":00:00'");
        if (StringUtils.isNotEmpty(inTrx.getEnd_time())) sqlVo.appendSql("and test_time <= '" + inTrx.getEnd_time() + ":00:00'");
        sqlVo.appendSql(")tbl ORDER BY text");
        return sqlServer_query(sqlVo);
    }






    /*** MySql QueryRunner查询 **/
    private XpconditionO mySql_query(SqlVo sqlVo) {
        XpconditionO outTrx = new XpconditionO();
        List<ConditionOption> resultDataList = new ArrayList<>();
        try (Connection conn = JdbcUtils.getMySQLConnection()){
            long lBeginTimeTmp = System.currentTimeMillis();
            logger.info("SQL: " + sqlVo.getSql());
            resultDataList = qr.query(conn, sqlVo.getSql(), new BeanListHandler<>(ConditionOption.class));
            logger.info("execution {}ms", (System.currentTimeMillis() - lBeginTimeTmp));
            resultDataList = resultDataList.stream().sorted((o1, o2) -> compareTo2(o1.getText(), o2.getText())).collect(Collectors.toList()); //排序
            outTrx.setRtn_code("0000000");
            outTrx.setRtn_mesg("success");
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            e.printStackTrace();
            outTrx.setRtn_code("0000003");
            outTrx.setRtn_mesg(e.getMessage());
        }
        outTrx.setOary(resultDataList);
        outTrx.setTbl_cnt(resultDataList.size());
        return outTrx;
    }

    /*** SqlServer QueryRunner查询 **/
    private XpconditionO sqlServer_query(SqlVo sqlVo) {
        XpconditionO outTrx = new XpconditionO();
        List<ConditionOption> resultDataList = new ArrayList<>();
        try (Connection conn = JdbcUtils.getSqlServerConnection()){
            long lBeginTimeTmp = System.currentTimeMillis();
            logger.info("SQL: " + sqlVo.getSql());
            resultDataList = qr.query(conn, sqlVo.getSql(), new BeanListHandler<>(ConditionOption.class));
            logger.info("execution {}ms", (System.currentTimeMillis() - lBeginTimeTmp));
            resultDataList = resultDataList.stream().sorted((o1, o2) -> compareTo2(o1.getText(), o2.getText())).collect(Collectors.toList()); //排序
            outTrx.setRtn_code("0000000");
            outTrx.setRtn_mesg("success");
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            e.printStackTrace();
            outTrx.setRtn_code("0000003");
            outTrx.setRtn_mesg(e.getMessage());
        }
        outTrx.setOary(resultDataList);
        outTrx.setTbl_cnt(resultDataList.size());
        return outTrx;
    }

}